package com.xvaca.spcms.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.xvaca.spcms.bean.ChannelBean;
import com.xvaca.spcms.db.DBConnector;

public class ChannelDAO {
	
	private static final Logger logger = Logger.getLogger(ChannelDAO.class);
	
	private static String INSERT_CHANNEL_SQL = "INSERT INTO channels (name, cmd, code, sync_url, price, ip, status, description, created_at, updated_at) " +
			"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	
	private static String UPDATE_CHANNEL_SQL = "update channels set name = ?, cmd = ?, code = ?, sync_url = ?, price = ?, ip = ?, status = ?, description = ?, updated_at = ? where id = ?";
	
	private static String SELECT_CHANNEL_SQL = "SELECT * FROM channels order by id";
	
	private static String SELECT_CHANNEL_BY_ID_SQL = "SELECT * FROM channels where id = ?";

	/**
	 * 插入通道
	 * @param channel
	 */
	public void insertChannel(ChannelBean channel) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = DBConnector.getConnection();
			pstmt = conn.prepareStatement(INSERT_CHANNEL_SQL);
			pstmt.setString(1, channel.getName());
			pstmt.setString(2, channel.getCmd().toUpperCase());
			pstmt.setString(3, channel.getCode());
			pstmt.setString(4, channel.getSyncUrl());
			pstmt.setInt(5, channel.getPrice());
			pstmt.setString(6, channel.getIp());
			pstmt.setInt(7, channel.getStatus());
			pstmt.setString(8, channel.getDesc());
			pstmt.setString(9, channel.getCreatedAt());
			pstmt.setString(10, channel.getUpdatedAt());
			
			pstmt.execute();
			
		} catch (Exception e) {
			logger.error("插入通道信息异常", e);
		} finally {
			DBConnector.closeAll(conn, pstmt, null);
		}
		
	}
	
	/**
	 * 更新通道
	 * @param channel
	 */
	public void updateChannel(ChannelBean channel) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = DBConnector.getConnection();
			pstmt = conn.prepareStatement(UPDATE_CHANNEL_SQL);
			pstmt.setString(1, channel.getName());
			pstmt.setString(2, channel.getCmd().toUpperCase());
			pstmt.setString(3, channel.getCode());
			pstmt.setString(4, channel.getSyncUrl());
			pstmt.setInt(5, channel.getPrice());
			pstmt.setString(6, channel.getIp());
			pstmt.setInt(7, channel.getStatus());
			pstmt.setString(8, channel.getDesc());
			pstmt.setString(9, channel.getUpdatedAt());
			pstmt.setInt(10, channel.getId());
			
			pstmt.execute();
			
		} catch (Exception e) {
			logger.error("更新通道信息异常", e);
		} finally {
			DBConnector.closeAll(conn, pstmt, null);
		}
		
	}
	
	/**
	 * 根据ID获取通道
	 * @param id
	 * @return
	 */
	public ChannelBean getChannelById(int id) {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ChannelBean channel = null;
		
		try {
			conn = DBConnector.getConnection();
			pstmt = conn.prepareStatement(SELECT_CHANNEL_BY_ID_SQL);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			
			if (rs.next()) {
				channel = new ChannelBean();
				channel.setCmd(rs.getString("cmd"));
				channel.setCode(rs.getString("code"));
				channel.setCreatedAt(rs.getString("created_at"));
				channel.setDesc(rs.getString("description"));
				channel.setId(rs.getInt("id"));
				channel.setIp(rs.getString("ip"));
				channel.setName(rs.getString("name"));
				channel.setPrice(rs.getInt("price"));
				channel.setStatus(rs.getInt("status"));
				channel.setSyncUrl(rs.getString("sync_url"));
				channel.setUpdatedAt(rs.getString("updated_at"));
			}
		} catch (Exception e) {
			logger.error("查询通道列表信息异常", e);
		} finally {
			DBConnector.closeAll(conn, pstmt, rs);
		}
		
		return channel;
		
	}
	
	public List<ChannelBean> getChannelList() {
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<ChannelBean> list = new ArrayList<ChannelBean>();
		
		try {
			conn = DBConnector.getConnection();
			pstmt = conn.prepareStatement(SELECT_CHANNEL_SQL);
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				ChannelBean channel = new ChannelBean();
				channel.setCmd(rs.getString("cmd"));
				channel.setCode(rs.getString("code"));
				channel.setCreatedAt(rs.getString("created_at"));
				channel.setDesc(rs.getString("description"));
				channel.setId(rs.getInt("id"));
				channel.setIp(rs.getString("ip"));
				channel.setName(rs.getString("name"));
				channel.setPrice(rs.getInt("price"));
				channel.setStatus(rs.getInt("status"));
				channel.setSyncUrl(rs.getString("sync_url"));
				channel.setUpdatedAt(rs.getString("updated_at"));
				
				list.add(channel);
			}
		} catch (Exception e) {
			logger.error("查询通道列表信息异常", e);
		} finally {
			DBConnector.closeAll(conn, pstmt, rs);
		}
		
		return list;
		
	}
	
}
